package com.guigu.service_user.mapper;

import com.guigu.service_user.entity.NewUser;
import com.guigu.service_user.entity.TbUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 用户表 Mapper 接口
 * </p>
 *
 * @author jobob
 * @since 2020-07-09
 */
public interface TbUserMapper extends BaseMapper<TbUser> {


    @Select("<script>SELECT  u.*,c.usercount usercount,c.num num FROM (SELECT a.username uname,COUNT(a.username) usercount ,SUM(a.pay_money) num\n" +
            "            FROM qingcheng_order.`tb_order` a,qingcheng_user.`tb_user` b WHERE a.username=b.username GROUP BY b.username) c RIGHT JOIN qingcheng_user.tb_user u\n" +
            "            ON c.uname = u.username\n" +
            "            where 1=1\n" +
            "            <if test=\"map.created!=null and map.created != '' \">\n" +
            "                and DATE_FORMAT(u.created,'%Y-%m-%d')=#{map.created}\n" +
            "            </if>\n" +
            "            <if test=\"map.nickName!=null and map.nickName != '' \">\n" +
            "                and u.nick_name like #{map.nickName}\n" +
            "            </if>\n" +
            "            GROUP BY u.username</script>")
    public List<NewUser> selByCount(@Param("map")Map<String,Object> map);

}
